package com.gack.business.dao;

import java.math.BigInteger;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.Query;
import javax.transaction.Transactional;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

/**
 * 
* @ClassName: UserManageDao 
* @Description: TODO(这里用一句话描述这个类的作用) 
* @author (ZhangXinYu)  
* @date 2018年6月19日 下午3:00:04 
* @version V1.0
 */
@Repository
public class UserManageDao {
	@Autowired
	private EntityManager entityManager;
	
	/**
	 * 
	* @Title: getUsers 
	* @Description: TODO(这里用一句话描述这个方法的作用) 
	* @param @param startTime	开始时间
	* @param @param endTime		结束时间
	* @param @param minAmount	最小消费金额
	* @param @param maxAmount	最大消费金额
	* @param @param searchType	查询类型  0 无条件查询 1昵称查询 2订单号查询
	* @param @param keyWord		查询关键词
	* @param @return    入参
	* @return List<Usernobind>    返回类型
	* @author （ZhangXinYu） 
	* @throws
	* @date 2018年6月19日 下午3:11:40 
	* @version V1.0
	 */
	public List<Usernobind> getUsers(String startTime, String endTime, Integer minAmount, Integer maxAmount,
			Integer searchType, String keyWord,Integer page,Integer size){
		List<Usernobind> usernobinds = null;
		String jpql = "select "
				+ " u.id as id,IFNULL(u.nickname,u.username) as nickname,u.username as username,"
				+ " IFNULL(u.expense_amount,0) as amount,IFNULL(u.create_office_num ,0)as officenum, "
				+ " (IFNULL(u.create_meeting_num,0) + IFNULL( u.join_video_num,0)) as videonum,u.is_check as ischeck,u.status, "
				+ " u.createtime,u.is_pledge as ispledge "
				+ " from user u ";
		if(searchType == 0){
			jpql += " WHERE 1 = 1 ";
		}else if (searchType == 1) {
			jpql += " WHERE u.nickname like :keyword "
					+ " and 1 = 1 ";
		}else if(searchType == 2){
			jpql += " LEFT JOIN orders o on o.user_id = u.id"
					+ " WHERE o.order_id = :keyword"
					+ " and 1 = 1 ";
		}
		if(startTime != null && !startTime.equals("")){
			SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS Z");
			try {
				Date d = format.parse(startTime.replace("Z", " UTC"));
				format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				startTime = format.format(d);
			} catch (ParseException e) {
				e.printStackTrace();
			}//注意是空格+UTC
			jpql +=" and u.createtime > '"+startTime+"'";
		}
		if(endTime != null && !endTime.equals("")){
			SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS Z");
			try {
				Date d = format.parse(endTime.replace("Z", " UTC"));
				format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				endTime = format.format(d);
			} catch (ParseException e) {
				e.printStackTrace();
			}//注意是空格+UTC
			jpql +=" and u.createtime < '"+endTime+"'";
		}
		if (minAmount != null) {
			jpql += " and IFNULL(u.expense_amount,0) >= " + minAmount ;
		}
		if (maxAmount != null) {
			jpql += " and IFNULL(u.expense_amount,0) <= " + maxAmount ; 
		}
		jpql += " order by u.createtime desc";
		Query query = entityManager.createNativeQuery(jpql, Usernobind.class);
		if(searchType == 1 ){
			query.setParameter("keyword", "%"+keyWord+"%");
		}else if(searchType == 2){
			query.setParameter("keyword", keyWord);
		}
		usernobinds = (List<Usernobind>)query.setFirstResult(page*size).setMaxResults(size).getResultList();
		return usernobinds;
	}
	
	/*
	 * 查询用户列表总数
	 */
	public long getUsersTotal(String startTime, String endTime, Integer minAmount, Integer maxAmount,
			Integer searchType, String keyWord){
		BigInteger total = new BigInteger("0");
		String jpql = "select "
				+ " count(u.id) as count"
				+ " from user u ";
		if(searchType == 0){
			jpql += " where 1 = 1 ";
		}
		else if (searchType == 1) {
			jpql += " WHERE u.nickname like :keyword "
					+ " and 1 = 1 ";
		}else if(searchType == 2){
			jpql += " LEFT JOIN orders o on o.user_id = u.id"
					+ " WHERE o.order_id = :keyword"
					+ " and 1 = 1 ";
		}
		if(startTime != null && !startTime.equals("")){
			SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS Z");
			try {
				Date d = format.parse(startTime.replace("Z", " UTC"));
				format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				startTime = format.format(d);
			} catch (ParseException e) {
				e.printStackTrace();
			}//注意是空格+UTC
			jpql +=" and u.createtime > '"+startTime+"'";
		}
		if(endTime != null && !endTime.equals("")){
			SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS Z");
			try {
				Date d = format.parse(endTime.replace("Z", " UTC"));
				format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				endTime = format.format(d);
			} catch (ParseException e) {
				e.printStackTrace();
			}//注意是空格+UTC
			jpql +=" and u.createtime < '"+endTime+"'";
		}
		if (minAmount != null) {
			jpql += " and IFNULL(u.expense_amount,0) >= " + minAmount ;
		}
		if (maxAmount != null) {
			jpql += " and IFNULL(u.expense_amount,0) <= " + maxAmount ; 
		}
		Query query = entityManager.createNativeQuery(jpql);
		if(searchType == 1 ){
			query.setParameter("keyword", "%"+keyWord+"%");
		}else if(searchType == 2){
			query.setParameter("keyword", keyWord);
		}
		total = (BigInteger) query.getSingleResult();
		return total.longValue();
	}
	
	public List<Usernobind> getExportUsers(String startTime, String endTime, Integer minAmount, Integer maxAmount,
			Integer searchType, String keyWord){
		List<Usernobind> usernobinds = null;
		String jpql = "select "
				+ " u.id as id,IFNULL(u.nickname,u.username) as nickname,u.username as username,"
				+ " IFNULL(u.expense_amount,0) as amount,IFNULL(u.create_office_num ,0)as officenum, "
				+ " (IFNULL(u.create_meeting_num,0) + IFNULL( u.join_video_num,0)) as videonum,u.is_check as ischeck,u.status, "
				+ " u.createtime,u.is_pledge as ispledge "
				+ " from user u ";
		if(searchType == 0){
			jpql += " WHERE 1 = 1 ";
		}else if (searchType == 1) {
			jpql += " WHERE u.nickname like :keyword "
					+ " and 1 = 1 ";
		}else if(searchType == 2){
			jpql += " LEFT JOIN orders o on o.user_id = u.id"
					+ " WHERE o.order_id = :keyword"
					+ " and 1 = 1 ";
		}
		if(startTime != null && !startTime.equals("")){
			SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS Z");
			try {
				Date d = format.parse(startTime.replace("Z", " UTC"));
				format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				startTime = format.format(d);
			} catch (ParseException e) {
				e.printStackTrace();
			}//注意是空格+UTC
			jpql +=" and u.createtime > '"+startTime+"'";
		}
		if(endTime != null && !endTime.equals("")){
			SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS Z");
			try {
				Date d = format.parse(endTime.replace("Z", " UTC"));
				format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				endTime = format.format(d);
			} catch (ParseException e) {
				e.printStackTrace();
			}//注意是空格+UTC
			jpql +=" and u.createtime < '"+endTime+"'";
		}
		if (minAmount != null) {
			jpql += " and IFNULL(u.expense_amount,0) >= " + minAmount ;
		}
		if (maxAmount != null) {
			jpql += " and IFNULL(u.expense_amount,0) <= " + maxAmount ; 
		}
		jpql += " order by u.createtime desc";
		Query query = entityManager.createNativeQuery(jpql, Usernobind.class);
		if(searchType == 1 ){
			query.setParameter("keyword", "%"+keyWord+"%");
		}else if(searchType == 2){
			query.setParameter("keyword", keyWord);
		}
		usernobinds = (List<Usernobind>)query.getResultList();
		return usernobinds;
	}
	
	/**
	 * 获取用户使用中订单数量
	 */
	public long getUserUseingOrderCount(String userid){
		BigInteger count = new BigInteger("0");
		String jpql = "select count(u.id) as count from user u "
				+ " left join orders o on u.id = o.user_id"
				+ " where o.status not in ('2','3','8','9','10')"
				+ " and u.id = :userid";
		Query query = entityManager.createNativeQuery(jpql);
		query.setParameter("userid", userid);
		count = (BigInteger)query.getSingleResult();
		return count.longValue();
	}
	
	@Transactional
	public int updateUserStatus(String userid,Integer status){
		int count = 0;
		String jpql = "update user set status = :status where id = :userid";
		Query query = entityManager.createNativeQuery(jpql);
		query.setParameter("status", status);
		query.setParameter("userid", userid);
		count = query.executeUpdate();
		return count;
	}
}
